<?php

class UploadController extends Controller
{
	

	// Uncomment the following methods and override them if needed
	/*
	public function filters()
	{
		// return the filter configuration for this controller, e.g.:
		return array(
			'inlineFilterName',
			array(
				'class'=>'path.to.FilterClass',
				'propertyName'=>'propertyValue',
			),
		);
	}

	public function actions()
	{
		// return external action classes, e.g.:
		return array(
			'action1'=>'path.to.ActionClass',
			'action2'=>array(
				'class'=>'path.to.AnotherActionClass',
				'propertyName'=>'propertyValue',
			),
		);
	}
	*/
	public function actions(){
	
	   return array(
            'upload'=>'application.controllers.upload.UploadFileAction',
            'upload_sales'=>'application.controllers.upload.UploadSalesAction',
        );
	
	}
	
	
/*	public function insertSales($s){
	   if(is_array($s)){
	      //print_r($s); 	
	      try{
				$connection = Yii::app()->db;
				$sql = "INSERT INTO gn_sales_(member_id,product_id,quantity,amount,dt_sale)
						VALUES (:member_id,:product_id,:quantity,:amount,:dt_sale)";
						
				$command = $connection->createCommand($sql);
				$command->bindParam(":member_id", $s['A'],PDO::PARAM_STR);
				$command->bindParam(":product_id", $s['B'],PDO::PARAM_STR);
				$command->bindParam(":quantity", $s['C'],PDO::PARAM_STR);
				$command->bindParam(":amount", $s['D'],PDO::PARAM_STR);
				$dtSale = date('Ymd',strtotime($s['E']));
				$command->bindParam(":dt_sale",$dtSale,PDO::PARAM_STR);
				$command->execute();
			}catch(Exception $e){
			    echo $e->getMessage();
			}
		}
	}
	*/


	public function insertSales($s){
	   if(is_array($s)){
	      //print_r($s); 	
	      try{
				$connection = Yii::app()->db;
				$sql = "INSERT INTO gn_sales(member_id,product_id,quantity,amount,dt_sale)
						VALUES (:member_id,:product_id,:quantity,:amount,:dt_sale)";
						
				$command = $connection->createCommand($sql);
				$command->bindParam(":member_id", $s['A'],PDO::PARAM_STR);
				$command->bindParam(":product_id", $s['B'],PDO::PARAM_STR);
				$command->bindParam(":quantity", $s['C'],PDO::PARAM_STR);
				$command->bindParam(":amount", $s['D'],PDO::PARAM_STR);
				$dtSale = date('Y-m-d H:i:s',strtotime($s['E']));
				$command->bindParam(":dt_sale",$dtSale,PDO::PARAM_STR);
				$command->execute();

			}catch(Exception $e){
			    echo $e->getMessage();
			}
		}
	}
	
	public function actionIndex()
	{
		//echo $_SERVER['DOCUMENT_ROOT'];
		$uploadFolder=dirname(__FILE__) . '/../../upload/'; 
		$this->render('index',array());
	}

	public function actionSales()
	{
		//echo $_SERVER['DOCUMENT_ROOT'];
		$uploadFolder=dirname(__FILE__) . '/../../upload/'; 
		$this->render('sales',array());
	}
	
	
	public function actionProcessFile(){
	   //extract contents of excel file and insert into sales table
	   header('Content-type: application/json');
       //$uploadFolder=$_SERVER['DOCUMENT_ROOT'] . '/greatnutrition/upload/'; 
	   //$processedFolder=$_SERVER['DOCUMENT_ROOT'] . '/greatnutrition/processed/'; 
	   
	   $uploadFolder=dirname(__FILE__) . '/../../upload/'; 
	   $processedFolder=dirname(__FILE__) .  '/../../processed/'; 
	   
	   
	   $isProcessed=false;
	 // try{
		  $phpExcelPath = Yii::getPathOfAlias('application.extensions.phpexcel.Classes');
		  spl_autoload_unregister(array('YiiBase','autoload'));
		  include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');
		  include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel/IOFactory.php');
		  spl_autoload_register(array('YiiBase','autoload')); 
		   if(is_dir($uploadFolder)){
			   if($dh=opendir($uploadFolder)){
				  while(($file = readdir($dh))!==false){
					  //identify file type
					    $salesFile = rtrim($uploadFolder,'/') . '/' .$file;
						if(is_file($salesFile)){
						  
						  /** PHPExcel_IOFactory */
						  $ft = PHPExcel_IOFactory::identify($salesFile);
						  //create a reader for the type
						  $objReader = PHPExcel_IOFactory::createReader($ft);
						  //load file to PHPExcel object
						  $objPHPExcel = $objReader->load($salesFile);
						  //dump data
						  $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
						  //insert sales data to database
						  //column headers in array 1
						  //dont include the headers
						  //var_dump($sheetData);
						  $arrSize = sizeof($sheetData);
						  for($i=2;$i<=$arrSize;$i++){
						     $this->insertSales($sheetData[$i]);
						  }
						  //move the file to the processed folder
						  rename($salesFile,rtrim($processedFolder,'/') . '/'.$file);
					  }
				  
				  }
				  $isProcessed=true;
				  
				 closedir($dh);  
				
			   }
			  

		   }
		    if($isProcessed){
			  echo CJSON::encode(array('flag'=>$isProcessed));
			}  
	  
	   Yii::app()->end();
	   
	}

    // TO BE MODIFIED FOR NEW SALES UPLOAD
	public function insertGnposSales($s){
	   if(is_array($s)){
	      try{
				$connection = Yii::app()->db;
				$sql = "INSERT INTO gn_sales_upload(gnpos_sales_id, member_id,member_name, dt_purchase,
						 product_code, quantity, bv_product_points, bv_earned_points)
						VALUES (:gnpos_sales_id,:member_id, :member_name, :dt_purchase, :product_code, 
							:quantity, :bv_product_points, :bv_earned_points)
						ON DUPLICATE KEY UPDATE quantity = :quantity,
						 bv_product_points = :bv_product_points, bv_earned_points = :bv_earned_points";

				$command = $connection->createCommand($sql);
				$command->bindParam(":gnpos_sales_id", $s[0],PDO::PARAM_STR);
				$command->bindParam(":member_id", $s[1],PDO::PARAM_STR);
				$command->bindParam(":member_name", $s[2],PDO::PARAM_STR);
				
				$dtSale = date('Y-m-d H:i:s',strtotime($s[3]));
				$command->bindParam(":dt_purchase",$dtSale,PDO::PARAM_STR);
				$command->bindParam(":bv_product_points", $s[4],PDO::PARAM_STR);

				$command->bindParam(":product_code", $s[5],PDO::PARAM_STR);
				$command->bindParam(":quantity", $s[6],PDO::PARAM_STR);
				
				$command->bindParam(":bv_earned_points", $s[7],PDO::PARAM_STR);
				$result = $command->execute();
			}catch(Exception $e){
			    echo $e->getMessage();
			}
		}
	}
	
	//TO BE MODIFIED FOR NEW SALES UPLOAD
	public function actionProcessSalesFile(){

	   //extract contents of excel file and insert into sales table
	   header('Content-type: application/json');
	   
	   $uploadFolder=dirname(__FILE__) . '/../../upload/'; 
	   $processedFolder=dirname(__FILE__) .  '/../../processed/'; 
	   
	   
	   $isProcessed=false;

		 
	   if(is_dir($uploadFolder)){
		   if($dh=opendir($uploadFolder)){
			  	while(($file = readdir($dh))!==false){
			  		$salesFile = rtrim($uploadFolder,'/') . '/' .$file;


			  		
				  	if(is_file($salesFile)){
				  		$upfile = fopen($salesFile,'r');
				  		$counter = 1;
				  		while (($data = fgetcsv($upfile)) != false) {
				  			if($counter == 1) {
				  				$counter++;
				  				continue;
				  			}
				  			else
				  			{
				  				$counter = 2;
				  				$this->insertGnposSales($data);	
				  			}
				  		}
						fclose($upfile);
						rename($salesFile,rtrim($processedFolder,'/') . '/'.$file);

					}

				}
			  
			}
			  
			$isProcessed=true;
			  
			closedir($dh);  
			
		}
	    

	    if($isProcessed){
		  echo CJSON::encode(array('flag'=>$isProcessed));
		}  
	  
	   	Yii::app()->end();
	   
	}
	
}